package com.project.dao.impl;

import com.project.bean.UserBean;
import com.project.dao.IUserDao;

import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl extends BaseDao implements IUserDao {
    @Override
    public void add(UserBean userBean) {

        this.setConnection();

        try {

            ps = con.prepareStatement("insert into t_user(u_name,u_password,u_birthday,u_grade)"+"values(?,?,?,?)");

            ps.setObject(1,userBean.getName());
            ps.setObject(2,userBean.getPassword());
            ps.setObject(3,userBean.getBirthday());
            ps.setObject(4,userBean.getGrade());

            ps.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        }finally {

                this.closeConnection();
        }
    }

    @Override
    public void delate(int id) {

        this.setConnection();


        try {

            ps = con.prepareStatement("delete from t_user where pk_userI =?");
            ps.setObject(1,id);

            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
       this.closeConnection();
        }
    }

    @Override
    public void update(int id, String password, String grade) {

        this.setConnection();

        try {
            ps = con.prepareStatement("update t_user set u_password=?,u_grade=? where pk_userId=? ");
            ps.setObject(1,password);
            ps.setObject(2,grade);
            ps.setObject(3,id);
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }
    }

    @Override
    public List<UserBean> findAll() {
        List<UserBean> list = new ArrayList<>();

        this.setConnection();

        try {

            ps = con.prepareStatement("select * from t_user");
            //将查询结果，存放至结果集对象中
            rs = ps.executeQuery();
            //将结果集指针指向下一条记录，如果返回值为false，表示没有下一条记录，遍历结束
            while (rs.next()){
                list.add(getUser());
            }
            System.out.println(list);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
         this.closeConnection();
        }
        return list;
    }

    @Override
    public UserBean findById(int id) throws SQLException {
            UserBean userBean =null;
            this.setConnection();

        try {

            ps = con.prepareStatement("select * from t_user where pk_userId=? ");
            ps.setObject(1,id);
            //将查询结果，存放至结果查询中
            rs = ps.executeQuery();
            if(rs.next()){

                userBean=getUser();
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
                this.closeConnection();
        }

        return  userBean;
    }

    @Override
    public List<UserBean> findByName(String name) {
        List<UserBean> list = new ArrayList<>();
        this.setConnection();


        try {

            ps = con.prepareStatement("select * from t_user where u_name like ?");
            ps.setObject(1,"%"+name+"%");
            //将查询结果，存放至结果查询中
            rs = ps.executeQuery();
            while (rs.next()){

                list.add(getUser());
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {

                this.closeConnection();
        }

        return list;
    }

    @Override
    public List<UserBean> findByBirthday(LocalDate bir1, LocalDate bir2) {
        List<UserBean> list = new ArrayList<>();
      this.setConnection();


        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:6789/db?characterEncoding=utf-8",
                    "root","lovo");
            ps = con.prepareStatement("select * from t_user where u_birthday>? and u_birthday<? ");
            ps.setObject(1,bir1);
            ps.setObject(2,bir2);
            //将查询结果，存放至结果查询中
            rs = ps.executeQuery();
            while (rs.next()){

                list.add(getUser());
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
          this.closeConnection();

        }

        return list;
    }

    @Override
    public List<UserBean> findByGrade(String grade) {
        List<UserBean> list = new ArrayList<>();
       this.setConnection();


        try {

            ps = con.prepareStatement("select * from t_user where u_grade= ?");
            ps.setObject(1,grade);
            //将查询结果，存放至结果查询中
            rs = ps.executeQuery();
            while (rs.next()){

                list.add(getUser());
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
           this.closeConnection();

        }

        return list;
    }

    @Override
    public UserBean login(String name, String password) {
        UserBean userBean = null;
     this.setConnection();
        try {

            ps = con.prepareStatement("select * from t_user where u_name=? and u_password=?");
            ps.setObject(1,name);
            ps.setObject(2,password);
            rs = ps.executeQuery();
            if (rs.next()){

               userBean= getUser();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
           this.closeConnection();
        }
        return userBean;
    }

    @Override
    public List<UserBean> findByItem(String name, LocalDate startDate, LocalDate endDate) {
        List<UserBean> list = new ArrayList<>();
        String sql="select * from t_user where 1=1 ";
        List valueList = new ArrayList();
        if (name != null && name.length() !=0){
            sql+="and u_name like ? ";
            valueList.add("%"+name+"%");
        }
        if (startDate !=null){
            sql+="and u_birthday >= ? ";
            valueList.add(startDate);
        }
        if (endDate !=null){
            sql+="and u_birthday <= ? ";
            valueList.add(endDate);
        }
        this.setConnection();
        try {
            ps = con.prepareStatement(sql);
            for (int i = 0; i <valueList.size() ; i++) {
            ps.setObject(i+1,valueList.get(i));
            }
            rs=ps.executeQuery();
            while (rs.next()){
                list.add(getUser());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }
        return list;
    }

//    @Override
//    public List<UserBean> findByItem(String name, LocalDate startDate, LocalDate endDate) {
//        List<UserBean> list = new ArrayList<>();
//        this.setConnection();
//        try {
//            if (name==null && startDate==null && endDate==null){
//                ps = con.prepareStatement("select * from t_user");
//            }else if (name==null && startDate !=null &&endDate!=null){
//                ps = con.prepareStatement("select * from t_user where u_birthday>'"+startDate+"'and u_birthday<'"+endDate+"'");
//            }else if (name!=null && startDate !=null && endDate==null){
//                ps = con.prepareStatement("select * from t_user where u_name %"+name+"% and u_birthday >"+startDate+"");
//            }else if (name!=null && startDate==null && endDate !=null){
//                ps = con.prepareStatement("select * from t_user where u_name '%"+name+"%' and u_birthday <"+endDate+"");
//            }else if (name==null && startDate ==null && endDate!=null){
//                ps = con.prepareStatement("select * from t_user where u_birthday<"+endDate+"");
//            }else if (name==null && startDate != null && endDate==null){
//                ps = con.prepareStatement("select * from t_user where u_birthday>"+startDate+"");
//            }else if (name !=null&&startDate==null&&endDate==null){
//                ps = con.prepareStatement("select * from t_user where u_name like '%"+name+"%'");
//            }
//            rs = ps.executeQuery();
//            while(rs.next()){
//
//                list.add(getUser());
//            }
//            System.out.println(list);
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        return list;
//    }

    private UserBean getUser() throws SQLException {
        UserBean userBean = new UserBean();
        userBean = new UserBean();
        userBean.setId(rs.getInt("pk_userId"));
        userBean.setName(rs.getString("u_name"));
        userBean.setPassword(rs.getString("u_password"));
        userBean.setBirthday(LocalDate.parse(rs.getString("u_birthday")));
        userBean.setGrade(rs.getString("u_grade"));
        return userBean;
    }

    public static void main(String[] args) {
        IUserDao dao = new UserDaoImpl();
        dao.add(new UserBean("wang5","456789",LocalDate.parse("1987-02-17"),"管理员"));
//        dao.add(new UserBean("zhao6","666666",LocalDate.parse("2000-03-25"),"普通用户"));
//        dao.delate(1);
//        dao.update(2,"123789","超级用户");

//        System.out.println(dao.findAll());
//        System.out.println(dao.findById(3));
//        System.out.println(dao.findByName("h"));
//        System.out.println(dao.findByBirthday(LocalDate.parse("1996-11-03"),LocalDate.parse("2000-10-17")));
//        System.out.println(dao.findByGrade("超级用户"));

    }
}
